# coding=utf-8

import os
import sys
import re


reload(sys)
sys.setdefaultencoding('utf-8')


def course_ifdentify():
    return {"phonics":1,
            "practical_1":2,
            "practical_4":3,
            "junior":4,
            "high_school":5,
            "cet4":6,
            "tem4":7,
            "cet6":8,
            "ielts":9,
            "toefl":10,
            "gre":11,
            "sat":12,
            "practical_2":14,
            "practical_3":15,
    }

from openpyxl.reader.excel import load_workbook

# import schema.xlsx as xlsx
import xlsx

from finder import fs

reload(sys)
sys.setdefaultencoding('utf-8')

#  story source
def make_script(book_name):
    """"""
    wb = load_workbook(filename="xlsx/%s" % book_name)
    BookPrefix = re.split('\.', book_name)
    lines = []
    lines.append("source dml/%s/%s.sql;" % (book_name, BookPrefix[0] + "_word_group_story"))
    # lines.append("source dml/%s/%s.sql;" % (book_name, BookPrefix[0] + "_story_documents"))
    # for shtName in wb.get_sheet_names():
    #     lines.append("source dml/%s/%s.sql;" % (book_name, shtName))
    # fs.write("dml/%s.dml.sql" % book_name, lines)
    fo = open("dml/insert_word_group_story.dml.sql", "a")
    for line in lines:
        fo.write(line)
        fo.write("\n")
    # fs.write("dml/insert_word_group_story.dml.sql", lines)
    print "%s_insert_word_group_story.dml.sql" % book_name

    lines_update = []
    lines_update.append("source dml/%s/%s.sql;" % (book_name, "update_" + BookPrefix[0] + "_word_group_story"))
    # lines.append("source dml/%s/%s.sql;" % (book_name, BookPrefix[0] + "_story_documents"))
    # for shtName in wb.get_sheet_names():
    #     lines.append("source dml/%s/%s.sql;" % (book_name, shtName))
    # fs.write("dml/%s.dml.sql" % book_name, lines)
    fo = open("dml/update_word_group_story.dml.sql" , "a")
    for line2 in lines_update:
        fo.write(line2)
        fo.write("\n")
    # fs.write("dml/insert_word_group_story.dml.sql", lines)
    print "update_%s_word_group_story.dml.sql" % BookPrefix[0]


def process_dml(book_name):
    """cet4_story_documents"""
    if not os.path.exists("dml/%s" % book_name):
        os.makedirs("dml/%s" % book_name)
    wb = load_workbook(filename="xlsx/%s" % book_name, data_only=True)
    BookPrefix = re.split('\.', book_name)
    # print(BookPrefix)
    for shtName in wb.get_sheet_names():
        if shtName == BookPrefix[0] + '_story_documents':
            records = build_dml(wb, shtName,book_name)
            fs.write("dml/%s/%s.sql" % (book_name, BookPrefix[0] + "_word_group_story"), records) ##BookPrefix[0] + "_word_group_story_documents")
            print("%s.sql." %  BookPrefix[0] + "_word_group_story")
        # if shtName == BookPrefix[0] + '_story_documents':      ###UPDATE word_group_story set stroy="" where id=;
            update_data = update_story_context(book_name,shtName)
            print(shtName)
            fs.write("dml/%s/update_%s.sql" % (book_name,  BookPrefix[0] + "_word_group_story"), update_data)


def prepare(value):
    return value.replace('\'', '\\\'').strip()


def build_dml(wb, sheet_name,book_name):
    """"""
    ws = wb.get_sheet_by_name(sheet_name)
    rtn = []
    mr = ws.max_row + 1
    mc = ws.max_column + 1
    if mr <= 2:
        return rtn

    line = "insert into word_group_story ("
    # line = "insert into word_group_story ("
    for c in range(1, mc):
        value = str(ws.cell(row=1, column=c).value)
        if c == mc - 1:
            line = line + value + ") values"
        else:
            line = line + value + ", "
        print line
    rtn.append(line)

    coursename_identify = course_ifdentify()
    BookPrefix = re.split('\.', book_name)
    for r in range(2, mr):###列序号
        line = "("
        for c in range(1, mc):
            if c == 1:
                cell = ws.cell(row=r, column=c).value
                value = coursename_identify[BookPrefix[0]] * 100000 + int(cell)
            else:
                cell = ws.cell(row=r, column=c)
                value = prepare(str(cell.value))

            if xlsx.is_non_wrap(ws, c):
                value = value
            else:
                if value == "None":
                    value = 'NULL'
                else:
                    value = "'" + str(value) + "'"

            if c == mc - 1:
                line = line + value + ")"
            else:
                line = line + value + ", "
        if r == mr - 1:
            line = line + ";"
        else:
            line = line + ","
        rtn.append(line)
    return rtn


def update_story_context(book_name,sheet_name):
    BookPrefix = re.split('\.',book_name)
    if sheet_name == BookPrefix[0] + "_story_documents":
        wb = load_workbook(filename="xlsx/%s" % book_name, data_only=True)
        ws = wb.get_sheet_by_name(sheet_name)
        mr = ws.max_row + 1
        mc = ws.max_column + 1
        group_id = 0   ###str(ws.cell(row=2, column=1).value)  ##story_id===>group_id
        # serial = str(ws.cell(row=1, column=1).value)  ##story_id===>group_id
        line = "UPDATE word_group_story SET "
        rtn = []

        serial = []   ###str(ws.cell(row=2, column=2).value)
        for r in range(2, mr):
            groupId = ws.cell(row=r, column=1).value
            storyDocument = ws.cell(row=r, column=2).value

            coursename_identify = course_ifdentify()
            course_groupPrefix = coursename_identify[BookPrefix[0]] * 100000 + int(groupId)
            line += "story=\"" + storyDocument + "\"" + " where id=\"" + str(course_groupPrefix) + "\";"

            # print(line)
            # serial = serial_value
            # group_id = str(ws.cell(row=r, column=1).value)
            rtn.append(line)
            line = "UPDATE word_group_story SET "
    else:
        rtn = []

        # if r == mr - 1:
        #     line = line + ";"
        # else:
        #     line = line + ","
    return rtn
